#/usr/bin/env python
# -*- coding: utf-8 -*-

''' Autor: Bruno Alves '''

import sqlite3
import xlrd


'''Class to convert the information from the xls file to a sqlite database.'''
class Database:
    @staticmethod
    def createDatabase():
        '''Method to create the database and add the data to it.'''
        
        connection = sqlite3.connect("Database.db")
        cursor = connection.cursor() 

        cursor.execute('''drop table if exists Data''')
        connection.commit()

        cursor.execute('''create table if not exists Data (id INTEGER,   
                                                           establishment TEXT,
                                                           organic_unit TEXT,
                                                           education_level TEXT,
                                                           course TEXT,
                                                           area TEXT,
                                                           year_95_96 INTEGER,
                                                           year_96_97 INTEGER,
                                                           year_97_98 INTEGER,
                                                           year_98_99 INTEGER,
                                                           year_99_00 INTEGER,
                                                           year_00_01 INTEGER,
                                                           year_01_02 INTEGER,
                                                           year_02_03 INTEGER,
                                                           year_03_04 INTEGER,
                                                           year_04_05 INTEGER,
                                                           year_05_06 INTEGER,
                                                           year_06_07 INTEGER,
                                                           year_07_08 INTEGER,
                                                           year_08_09 INTEGER,
                                                           year_09_10 INTEGER,
                                                           year_10_11 INTEGER)''')


        wb = xlrd.open_workbook('Inscritos_2010-2011.xls')

        listEstablishment = []
        listUnit = []
        listLevel = []
        listCourse = []
        listArea = []
        listYear9596 = []
        listYear9697 = []
        listYear9798 = []
        listYear9899 = []
        listYear9900 = []
        listYear0001 = []
        listYear0102 = []
        listYear0203 = []
        listYear0304 = []
        listYear0405 = []
        listYear0506 = []
        listYear0607 = []
        listYear0708 = []
        listYear0809 = []
        listYear0910 = []
        listYear1011 = []

        i = 0
        sh = wb.sheet_by_index(30)


        for row in range(4, 11955):   
            if (sh.cell_value(row,0)) == '': 
                listEstablishment = listEstablishment
                pass
            else:
                listEstablishment = (sh.cell_value(row,0))
                pass

            if (sh.cell_value(row, 1)) == '':
                listUnit = listUnit
                pass
            else:
                listUnit = (sh.cell_value(row,1))
                pass

            if (sh.cell_value(row, 2)) == '':
                listLevel = listLevel
                pass
            else:
                listLevel = (sh.cell_value(row, 2))
                pass


            listCourse = (sh.cell_value(row, 3))
 
            listArea = (sh.cell_value(row, 4))

            listYear9596 = (sh.cell_value(row, 7))

            listYear9697 = (sh.cell_value(row, 10))
            
            listYear9798 = (sh.cell_value(row, 13))
            
            listYear9899 = (sh.cell_value(row, 16))
            
            listYear9900 = (sh.cell_value(row, 19))

            listYear0001 = (sh.cell_value(row, 22))
            
            listYear0102 = (sh.cell_value(row, 25))
            
            listYear0203 = (sh.cell_value(row, 28))
            
            listYear0304 = (sh.cell_value(row, 31))
            
            listYear0405 = (sh.cell_value(row, 34))
            
            listYear0506 = (sh.cell_value(row, 37))
            
            listYear0607 = (sh.cell_value(row, 40))
            
            listYear0708 = (sh.cell_value(row, 43))
            
            listYear0809 = (sh.cell_value(row, 47))
            
            listYear0910 = (sh.cell_value(row, 50))
            
            listYear1011 = (sh.cell_value(row, 53))

            cmd = '''insert into Data (id,
                                       establishment,
                                       organic_unit,
                                       education_level,
                                       course,
                                       area,
                                       year_95_96,
                                       year_96_97,
                                       year_97_98,
                                       year_98_99,
                                       year_99_00,
                                       year_00_01,
                                       year_01_02,
                                       year_02_03,
                                       year_03_04,
                                       year_04_05,
                                       year_05_06,
                                       year_06_07,
                                       year_07_08,
                                       year_08_09,
                                       year_09_10,
                                       year_10_11)
                                       values ("{0}", "{1}", "{2}", "{3}", "{4}",
                                       "{5}", "{6}", "{7}", "{8}", "{9}", "{10}",
                                       "{11}", "{12}", "{13}", "{14}", "{15}", "{16}",
                                       "{17}", "{18}", "{19}", "{20}", "{21}")'''.\
                                       format(i,
                                       str(listEstablishment.encode('utf-8').replace( '"', '♦' )),
                                       str(listUnit.encode('utf-8').replace( '"', '♦' )),
                                       str(listLevel.encode('utf-8').replace( '"', '♦' )),
                                       str(listCourse.encode('utf-8').replace( '"', '♦' )),
                                       str(listArea.encode('utf-8').replace( '"', '♦' )),
                                       listYear9596,
                                       listYear9697,
                                       listYear9798,
                                       listYear9899,
                                       listYear9900,
                                       listYear0001,
                                       listYear0102,
                                       listYear0203,
                                       listYear0304,
                                       listYear0405,
                                       listYear0506,
                                       listYear0607,
                                       listYear0708,
                                       listYear0809,
                                       listYear0910,
                                       listYear1011)
            
            cursor.execute(cmd)    
            i = i + 1  
            pass
                
        connection.commit()
        connection.close()

        pass
        pass
             

